import pandas as pd
import numpy as np
from utils.name_sanitizer import sanitize_name
from utils.type_detector import determine_column_type

def generate_sql(table_name: str, df: pd.DataFrame, batch_size: int) -> tuple:
    """生成建表SQL和插入数据SQL（分批）"""
    # 清理表名
    safe_table_name = sanitize_name(table_name)
    
    # 生成建表SQL
    create_table_sql = generate_create_table_sql(safe_table_name, df)
    
    # 生成分批插入SQL
    insert_sql_batches = generate_insert_sql_batches(safe_table_name, df, batch_size)
    
    return create_table_sql, insert_sql_batches

def generate_create_table_sql(table_name: str, df: pd.DataFrame) -> str:
    """生成建表SQL"""
    columns_sql = []
    for col_name, col_data in df.items():
        safe_col_name = sanitize_name(col_name)
        col_type = determine_column_type(col_data)
        columns_sql.append(f"`{safe_col_name}` {col_type}")
    
    return (
        f"DROP TABLE IF EXISTS `{table_name}`;\n"
        f"CREATE TABLE `{table_name}` (\n"
        f"  {',\n  '.join(columns_sql)}\n"
        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"
    )

def generate_insert_sql_batches(table_name: str, df: pd.DataFrame, batch_size: int) -> list:
    """生成分批插入SQL"""
    insert_sql_batches = []
    total_rows = len(df)
    
    # 如果没有数据，返回空列表
    if total_rows == 0:
        return insert_sql_batches
    
    # 计算批次数
    num_batches = (total_rows + batch_size - 1) // batch_size
    
    for batch_idx in range(num_batches):
        start_idx = batch_idx * batch_size
        end_idx = min((batch_idx + 1) * batch_size, total_rows)
        batch_df = df.iloc[start_idx:end_idx]
        
        values_list = []
        for _, row in batch_df.iterrows():
            row_values = []
            for val in row:
                if pd.isna(val) or val is None:
                    row_values.append("NULL")
                elif isinstance(val, (int, np.integer)):
                    row_values.append(str(val))
                elif isinstance(val, (float, np.floating)):
                    # 保留6位小数
                    if val.is_integer():
                        row_values.append(str(int(val)))
                    else:
                        row_values.append(f"{val:.6f}")
                else:
                    # 转义特殊字符并添加引号
                    escaped = str(val).replace("'", "''").replace("\\", "\\\\")
                    row_values.append(f"'{escaped}'")
            values_list.append(f"({', '.join(row_values)})")
        
        if values_list:
            insert_sql = (
                f"INSERT INTO `{table_name}` VALUES\n"
                f"{',\n'.join(values_list)};"
            )
            insert_sql_batches.append(insert_sql)
    
    return insert_sql_batches